const mysql = require('mysql2/promise');
require('dotenv').config();

// 先创建数据库
async function createDatabase() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT
  });
  
  await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME}`);
  await connection.end();
  console.log(`数据库 ${process.env.DB_NAME} 创建成功`);
}

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 测试数据库连接并初始化表
async function initDatabase() {
  try {
    // 先创建数据库
    await createDatabase();
    
    const connection = await pool.getConnection();
    console.log('数据库连接成功');
    
    // 创建网络状态表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS network_status (
        id INT AUTO_INCREMENT PRIMARY KEY,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        ping_ms FLOAT,
        download_speed FLOAT,
        upload_speed FLOAT,
        packet_loss FLOAT,
        bandwidth_usage FLOAT,
        connection_status ENUM('connected', 'disconnected', 'unstable') DEFAULT 'connected'
      );
    `);
    
    // 创建历史数据表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS network_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        date DATE NOT NULL,
        avg_ping FLOAT,
        max_download FLOAT,
        max_upload FLOAT,
        uptime_percentage FLOAT,
        total_traffic FLOAT
      );
    `);
    
    // 创建警报记录表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS network_alerts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        alert_type VARCHAR(50) NOT NULL,
        severity ENUM('low', 'medium', 'high') NOT NULL,
        description TEXT,
        is_resolved BOOLEAN DEFAULT FALSE
      );
    `);
    
    console.log('数据库表初始化完成');
    connection.release();
  } catch (error) {
    console.error('数据库连接或初始化失败:', error.message);
    throw error;
  }
}

module.exports = { pool, initDatabase };